var mysql = require('mysql');
var config = require('./defaultConfig');

var pool = mysql.createPool({
    host: config.database.HOST,
    user: config.database.USERNAME,
    password: config.database.PASSWORD,
    database: config.database.DATABASE,
    port: config.database.PORT
});


let allServices = {
    query: function (sql,values) {
        return new Promise((resolve, reject) => {
            pool.getConnection(function (err, connection) {
                if (err) {
                    reject(err)
                } else {                  
                    connection.query(sql,values, (err, rows) => {

                        if (err) {
                            reject(err)
                        } else {      
                            resolve(rows);
                        }
                        connection.release()
                    })
                }
            })
        })

    }   
}


// ======================后台管理用户模块
//查找用户
let finduser = function(value){
    let _sql = `select * from mall_user where username="${value}"`
    return allServices.query(_sql);
}
//新增用户
let adduser = function(value){
    let _sql = `insert into mall_user set username=?,password=?,email=?,phone=?,role=?,create_time=?,update_time=?`
    return allServices.query(_sql,value);
}
//查找所有的用户
let getuserlist = function(_limit,_number){
    let _sql = `select * from mall_user limit ${_limit}, ${_number};`
    return allServices.query(_sql);
}
//更新用户信息
let updateUserInfo = function(_username,_mobile,_pasw,_email,_creatTime){
    let _sql = `update mall_user set password="${_pasw}",email="${_email}",phone="${_mobile}",update_time="${_creatTime}" where username="${_username}"`
    return allServices.query(_sql);
}
//删除用户数据
let deleteUser = function(value){
    let _sql = `delete from mall_user where id in (${value})`
    return allServices.query(_sql,value);
}
//查询用户
let searchUser = function(username){
    let _sql = `select * from mall_user where username like "%${username}%"`
    return allServices.query(_sql);
}
// ======================后台管理用户模块



// ======================后台管理商品模块
//新增商品
let addProduct = function(value){
    let _sql = `insert into mall_product set category_id=?,name=?,subtitle=?,main_image=?,sub_images=?,detail=?,price=?,stock=?,status=?,create_time=?,update_time=?`
    return allServices.query(_sql,value);
}

//查找所有的商品
let getproductlist = function(_limit,_number){
    let _sql = `select * from mall_product;`
    return allServices.query(_sql);
}

//查找商品图片路径
let findimage = function(imageUrl){
    let _sql = `select * from mall_product where main_image = "${imageUrl}";`
    return allServices.query(_sql);
}
// ======================后台管理商品模块

// ======================数据总览模块
//获取上周的订单数�?
let getorderlist = function(imageUrl){
    let _sql = `SELECT DATE(DATE_SUB(subdate(curdate(),date_format(curdate(),'%w')-1), interval 1 day)) as lastweek  
    union all  
    SELECT  DATE(DATE_SUB(subdate(curdate(),date_format(curdate(),'%w')-1), interval 2 day)) as lastweek  
    union all  
    SELECT  DATE(DATE_SUB(subdate(curdate(),date_format(curdate(),'%w')-1), interval 3 day)) as lastweek  
    union all  
    SELECT  DATE(DATE_SUB(subdate(curdate(),date_format(curdate(),'%w')-1), interval 4 day)) as lastweek  
    union all  
    SELECT DATE(DATE_SUB(subdate(curdate(),date_format(curdate(),'%w')-1), interval 5 day)) as lastweek  
    union all  
    SELECT DATE(DATE_SUB(subdate(curdate(),date_format(curdate(),'%w')-1), interval 6 day)) as lastweek  
    union all  
    SELECT DATE(DATE_SUB(subdate(curdate(),date_format(curdate(),'%w')-1), interval 7 day)) as lastweek`
    return allServices.query(_sql);
}
let getlastweekorder = function(paymenttime){ 
    let _sql = `select sum(payment) average  from mall_order where date(payment_time) = "${paymenttime}";`
    return allServices.query(_sql);
}

let getlastweekordernum = function(payments,paymente){ 
    let _sql = `select count(*) num from mall_order where date(payment_time) BETWEEN "${payments}" AND "${paymente}";`
    return allServices.query(_sql);
}
// ======================数据总览模块


// ======================前台登录模块模块
//用户登录
let userlogin = function(username,userpwd){
    let _sql = `select * from mall_user where username="${username}" and password="${userpwd}"`
    return allServices.query(_sql);
}

//用户注册
let register = function(value){
    let _sql = `insert into users set username=?,userpwd=?,nickname=?;`
    return allServices.query(_sql,value);
}
// ======================前台登录模块模块


//=======================app模块
//获取分类列表
let getcategory = function(){
    let _sql = `select * from mall_category;`
    return allServices.query(_sql);
}
///获取商品列表
let getgoodlist = function(id){
    let _sql = `select * from mall_product where category_id = "${id}";`
    return allServices.query(_sql);
}

///获取商品详情信息
let getgoodinfo = function(id){
    let _sql = `select * from mall_product where id = "${id}";`
    return allServices.query(_sql);
}


///获取个人中心列表数据
let getpersonaldata = function(userid,status){
    let _sql = `select * from mall_order where user_id = "${userid}" and status = "${status}";`
    return allServices.query(_sql);
}

let orderitem = function(order_noo,user_ido){
    let _sql = `select * from mall_order_item where user_id = "${user_ido}" and order_no = "${order_noo}";`
    return allServices.query(_sql);
}

//获取分类列表大类和子类
let getcategoryed = function(){
    let _sql = `select * from mall_category where parent_id = 0;`
    return allServices.query(_sql);
}


//获取分类子分类
let getsoncate = function(id){
    let _sql = `select * from mall_category where parent_id ="${id}";`
    return allServices.query(_sql);
}

//=======================app模块

module.exports = {
    finduser,
    adduser,
    getuserlist,
    updateUserInfo,
    deleteUser,
    searchUser,
    addProduct,
    getproductlist,
    findimage,
    getorderlist,
    getlastweekorder,
    getlastweekordernum,
    userlogin,
    getcategory,
    getgoodlist,
    getgoodinfo,
    getpersonaldata,
    orderitem,
    getcategoryed,
    getsoncate
}
